﻿using System.Collections.Generic;
using System.Linq;
using WMSSyncData.Model;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.SqlDB
{
    public class Tddev108DAL : BaseSqlDBDAL<tddev108>
    {
        public bool CloseDetail(tddev108 tddev)
        {
            bool result = false;
            var sql = "UPDATE wms_T_InStockRowDetails SET Status='9' where BillNo ='{0}' and RelatedBill='{1}';";
            sql = string.Format(sql, tddev.wmso, tddev.orno, tddev.qrec);
            var updateRes = SqlDBHelper.ExcuteSQL(sql);
            if (updateRes > 0)
            {
                result = true;
            }
            return result;
        }

        public bool CloseMain(string bills)
        {
            bool result = false;
            var sql = @"Update wms_T_InStock Set Status='2' Where Status='1' AND BillNo In (" + bills + ")";

            //            var sql = @"Update wms_T_InStock Set Status='2' Where Status='1' AND BillNo In
            //(Select BillNo from wms_T_InStockRowDetails  GROUP BY billno HAVING Min(Status)='9')";
            var updateRes = SqlDBHelper.ExcuteSQL(sql);
            if (updateRes > 0)
            {
                result = true;
            }
            return result;
        }

        public List<tddev108> GetSyncData()
        {
            //            var sql = @"select distinct RelatedBill as orno,a.PartNumber as item,a.RealQty qrec,' ' as clot ,ISNULL(b.Storage_SN,' ') as cwar, 'A' as rclo ,getdate() as edte1,GETDATE() AS wdte1,GETDATE() edte2,' ' AS mess,0 AS refcntu,0 AS refcntd,  
            //ISNULL((SELECT COUNT(1) FROM wms_T_InStock WHERE Status=2 AND BillNo=b.billno GROUP BY BillNo),0) AS pdsn,1 as host
            //FROM wms_T_InStockRows a
            //left join wms_T_InStock b on b.BillNo=a.BillNo
            //where b.status='1'";
            var sql = @"SELECT BillNo as wmso,
isnull(RelatedBill,' ') as orno,isnull(PartNumber,' ') as item,temp.Qty qrec,' ' as clot ,ISNULL(Storage_SN,' ') as cwar, 'A' as rclo ,getdate() as edte1,GETDATE() AS wdte1,GETDATE() edte2,' ' AS mess,0 AS refcntu,0 AS refcntd,1 as host,
NEWID() AS guid,0 as pdsn
 FROM ( SELECT a.BillNo,a.Storage_SN,c.RelatedBill,c.PartNumber,SUM(c.PutawayQty) AS Qty FROM dbo.wms_T_InStock a
LEFT JOIN dbo.wms_T_InStockRows b ON a.BillNo=b.BillNo
LEFT JOIN dbo.wms_T_InStockRowDetails c ON b.BillNo=c.BillNo 
WHERE a.Status='1' AND a.BillType<>'返工入库单' AND a.BillType<>'半成品入库' GROUP BY a.Storage_SN,a.BillNo,c.RelatedBill,c.PartNumber) temp";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public bool UpdateUploadStatus(List<tddev108> list)
        {
            var result = false;
            var billNoList = list.Select(x => x.wmso).Distinct();
            if (billNoList.Count() > 0)
            {
                var sqlWhere = "where BillNo in (";
                foreach (var item in billNoList)
                {
                    sqlWhere = sqlWhere + "'" + item + "',";
                }
                sqlWhere = sqlWhere.TrimEnd(',');
                sqlWhere = sqlWhere + ")";
                var sql = "UPDATE  wms_T_InStock SET Status='2' " + sqlWhere;
                var updateRes = SqlDBHelper.ExcuteSQL(sql);
                if (updateRes > 0)
                {
                    result = true;
                }
            }
            return result;

        }
    }
}
